/**
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 *
 * @data:2017年10月17日
 * @author:MaShibo
 */
package com.wttech.dzzf.process.dao;

import java.util.List;

import javax.annotation.Resource;

import com.wtkj.vo.dzzf.base.kfwd.CardInfo;
import com.wtkj.vo.dzzf.base.kfwd.OBU;
import com.wttech.dzzf.process.model.ObuTemp;
import com.wttech.dzzf.process.model.UploadRsp;
import com.wttech.dzzf.process.model.user.Obu;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.wtkj.dao.impl.BaseDataTemplate;
import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfzx.ListRealRecord;
import org.springframework.util.CollectionUtils;

/**
 * 黑名单信息Dao实现
 *
 * @author MaShibo
 * @date 2017年10月17日
 */
@Repository
public class BlackListInfoDao extends BaseDataTemplate {


	@Resource(name = "jdbcTemplate")
	private JdbcTemplate testJdbcTemplate;

	@Resource(name = "dzzfJdbcTemplate")
	private JdbcTemplate dzzfJdbcTemplate;

	public Integer queryBlListRecordNum() {
		return dzzfJdbcTemplate.queryForObject("SELECT COUNT(1) FROM BL_LISTRECORD WHERE SENDID='9999999961010001' " +
				" AND STATUS <> 30001 AND TYPE IS NULL", Integer.class);
	}

	@SuppressWarnings("unchecked")
	public List<ListRealRecord> queryBllistRecordInfos(int startRow, int endRow) {
		String sql = " SELECT * " +
				"   FROM (SELECT A.*, ROWNUM AS RM FROM BL_LISTRECORD A  WHERE A.SENDID='9999999961070003' " +
				" AND STATUS <> 30001 AND TYPE1 IS NULL) " +
				"  WHERE RM >= ? " +
				"    AND RM <= ? ";
		return (List<ListRealRecord>) dzzfJdbcTemplate.query(sql, new Object[]{startRow, endRow},
				new ListResultSetExtractor(new ListRealRecord()));
	}

	@SuppressWarnings("unchecked")
	public List<ListRealRecord> queryObuBlListInfos(int startRow, int endRow) {
		String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*, ROWNUM AS RM\n" +
						"          FROM BL_LISTRECORD A\n" +
						"         WHERE A.OBUID IS NOT NULL\n" +
						"           AND A.TYPE1 IS NULL\n" +
						"           AND A.STATUS <> 30001)\n" +
						" WHERE RM >= ?\n" +
						"   AND RM <= ?";
		return (List<ListRealRecord>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new ListRealRecord()), startRow, endRow);
	}

	public int updateBlCardType(String listId, Integer type) {
		final String sql = "UPDATE BL_LISTRECORD A SET A.TYPE = ? WHERE LISTID=? ";
		return dzzfJdbcTemplate.update(sql, type, listId);
	}

	@SuppressWarnings("unchecked")
	public CardInfo queryCardInfoByNo(String ecardNo) {
		final String sql = "SELECT * FROM AC_CARDINFO A WHERE A.ECARDNO=? ";
		List<CardInfo> cardInfos = (List<CardInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), ecardNo);
		return CollectionUtils.isEmpty(cardInfos) ? null : cardInfos.get(0);
	}

	@SuppressWarnings("unchecked")
	public OBU queryObubyObuId(String obuId) {
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*,\n" +
						"               ROW_NUMBER() OVER(PARTITION BY A.CONTRACTNO ORDER BY A.OPTON DESC) AS RM\n" +
						"          FROM AC_OBU A\n" +
						"         WHERE A.CONTRACTNO = ?)\n" +
						" WHERE RM = 1";
		List<OBU> obus = (List<OBU>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new OBU()), obuId);
		return CollectionUtils.isEmpty(obus) ? null : obus.get(0);
	}

	public Obu queryBsObuById(String obuId) {
		final String sql = "SELECT * FROM BS_OBU O WHERE O.ID=? ";
		List<Obu> obus = testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Obu.class), obuId);
		return CollectionUtils.isEmpty(obus) ? null : obus.get(0);
	}

	public int updateBlObuType1(String listId, Integer type1) {
		final String sql = "UPDATE BL_LISTRECORD A SET A.TYPE1=? WHERE A.LISTID=? ";
		return dzzfJdbcTemplate.update(sql, type1, listId);
	}

	public int resetBlObuType1() {
		final String sql = "UPDATE BL_LISTRECORD A SET A.TYPE1=NULL WHERE A.SENDID='9999999961010001' AND STATUS <> 30001";
		return dzzfJdbcTemplate.update(sql);
	}

	public int queryBlListUploadNum(int type1){
		final String sql = "SELECT COUNT(1) FROM BL_LISTRECORD A WHERE A.TYPE1=? AND A.SENDID='9999999961010001' AND STATUS <> 30001 ";
		return dzzfJdbcTemplate.queryForObject(sql, Integer.class, type1);
	}

	public List<UploadRsp> queryBlListErrorRsp(){
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*, ROWNUM AS RM\n" +
						"          FROM BS_UPLOADRSP A\n" +
						"         WHERE A.ERRORCODE <> 707\n" +
						"           AND A.TYPE IN ('cardBlacklist', 'obuBlacklist'))\n" +
						" WHERE RM <= 1000";
		return testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UploadRsp.class));
	}

}
